Today, companies are under increasing pressure to provide accurate and reliable information faster and more efficiently to both internal and external decision makers. Often, the information required is financial data. However, the systems used to log transactions have traditionally been separate from the tools used to analyze the data. One example of a system used to log transactions is an Online Transaction Processing (OLTP) system. An example of a system used to analyze data is an Online Analytical Processing (OLAP) system.
OLTP systems manage applications that are transaction oriented, and are typically implemented using relational databases. Companies generally account for financial information in a ledger (which also may be referred to as a relational database or flat-file database) or other spreadsheet. These ledgers offer easy entry and maintenance of the accounting information. However, studying the information in the ledger is cumbersome or not possible.
A ledger may be created in a relational database to store financial transactions called “journal entries.” When a business transaction occurs, a journal entry is inserted into a table associated with the ledger. Journal entries may be inserted into a general ledger or sub-ledgers. In accounting, a “general ledger” (GL) is one of the main financial records of a business. The general ledger includes more or less all of the accounts which are reported on the business' financial statements, such as cash, accounts payable, and expense accounts. Each ledger in the general ledger typically keeps a balance, or the running total of money, in each account for which the business wishes to track. A sub-ledger may represent accounts payable or accounts receivable, or any other type of ledger, for example.
Each account for which journal entries are made is represented in a “chart of accounts.” A “chart of accounts” is a collection of account names. Small businesses may wish to track a small number of accounts, but large businesses commonly wish to track hundreds or even thousands of accounts. These accounts may correspond with different departments, organizations, or operational units within the business, as well as different product lines. Accounts may also correspond to different funds in educational or charitable institutions.
Although OLTP systems are simple to implement and efficient for processing transactions, generating reports from an OLTP system can be inefficient and time consuming. For example, it may be possible to determine revenues for one subsidiary over a predetermined amount of time. However, it may be very difficult or impossible to determine revenues for 15 foreign subsidiaries from selling a particular product during different times. The reason is that the data in the ledger is not stored in a manner where complex queries are easily made. Reports in OLTP systems are typically not pre-aggregated with balance information, and must therefore be executed each time the information is needed. Traditionally, complex reports generated from relational database systems have been run during a scheduled time, or from backup databases for this reason.
OLAP systems are capable of generating complex reports in an extremely efficient manner. OLAP systems support analysis and management reporting applications, and are often implemented in a multidimensional database that stores data in “cubes.” In contrast to relational database tables, which store data in two dimensions similar to a spreadsheet, OLAP cubes can store data in many dimensions, with each dimension representing a different view of the data. For example, a financial application may include dimensions for each fiscal quarter, a dimension for each year, and a dimension for each type of currency. Once data is stored in the OLAP system, all views are automatically populated, allowing all information defined by a particular view to be accessed instantaneously. However, these databases are much more difficult to populate with data and it is much more difficult to maintain the data already input.
Users of an OLTP system can perform complex reporting by periodically extracting data such as balances from various ledgers. For example, data may be extracted from the OLTP system every 6 hours, or daily. A user may then put the extracted data into a data warehouse that implements an OLAP system according to the user's specifications. However, this requires that the user build the data warehouse system as a custom solution. Applications can then be used to run reports against the data in the OLAP system. With such external data warehouses, all processes are custom, and do not produce real-time results, due to the time delay between each export operation. In addition, for maintenance-related to changes to the values or dimensions in the OLAP system, a user of such a system must create separate customer-specific processes to update the external data warehouse.
In view of the foregoing, it is desirable to maintain a OLTP system that is synchronized with an OLAP system. In addition, it is desirable to ensure that structural changes to an OLTP system are implemented simultaneously with corresponding structural changes to the OLAP system.